# 注：database名字为 phase2，三个题的table 分别为：homework_t1, homework_t2, homework_t3。
# 1
with tmp as 
(
	select 
		team, 
		year, 
		year - rank() over(partition by team order by year) as base 
	from phase2.homework_t1
) 
select 
	team 
from tmp 
group by team, base 
having count(*) = 3
;

# 2
with tmp as 
(
select
	id,
	time,
	price,
	nvl(lag(price) over(partition by id order by time), price) as lag_price,
	nvl(lead(price) over(partition by id order by time), price) as lead_price
from phase2.homework_t2
) 
select 
	id, 
	time, 
	price,
	case when price - lag_price > 0 and price - lead_price > 0 then "peak" else "trough" end as feature
from tmp
where (price - lag_price > 0 and price - lead_price > 0)
	or (price -lag_price < 0 and price - lead_price < 0)
;

# 3
## 3.1
select 
	id,
	(max(unix_timestamp(dt, "yyyy/MM/dd HH:mm")) - min(unix_timestamp(dt, "yyyy/MM/dd HH:mm"))) / 60 as duration,
	count(*) as pages
from phase2.homework_t3
group by id
;
## 3.2
select 
	id,
	(max(unix_timestamp(dt, "yyyy/MM/dd HH:mm")) - min(unix_timestamp(dt, "yyyy/MM/dd HH:mm"))) / 60 as duration,
	count(*) as pages
from (
	select 
		id,
		dt,
		sum(mark) over(partition by id order by dt rows between unbounded preceding and current row) as gid
	from 
	(
		select 
			id,
			dt,
			case when diff > 30*60 then 1 else 0 end as mark
		from
		(
			select 
				id,
				dt, 
				unix_timestamp(dt, "yyyy/MM/dd HH:mm") - unix_timestamp(lag(dt) over(partition by id order by dt), "yyyy/MM/dd HH:mm") as diff
			from phase2.homework_t3
		) o 
	) o 
) o 
group by id, gid
;


